package com.miage.kangourou.server.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.miage.kangourou.client.beans.Evenement;
import com.miage.kangourou.shared.Util;

public class EvenementDao extends GenericDao<Evenement> {

	public EvenementDao(Connection connect) {
		super(connect);
	}

	public boolean alreadyExist(Evenement evt) throws SQLException {
		boolean exist = false;

		if ((evt instanceof Evenement) && !evt.equals(null)) {

			ResultSet result = connect.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY).executeQuery(
					"SELECT * FROM EVENEMENT WHERE ID_EVENEMENT= "
							+ String.valueOf(evt.getId()));

			if (result.next()) {
				exist = true;
			}

			result.close();
		}

		return exist;
	}

	public void create(Evenement evt) throws SQLException {
		if ((evt instanceof Evenement) && !evt.equals(null)) {
			connect
					.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE)
					.executeUpdate(
							"INSERT INTO EVENEMENT (ID_SALLE, ID_TYPE, ID_ENSEIGNANT, ID_MATIERE, ID_GROUPE, DATE_DEBUT, DATE_FIN, COMMENTAIRE, ID_EVENEMENT_RECURRENCE) VALUES ("
									+ evt.getId_salle()
									+ ", "
									+ evt.getId_type()
									+ ", "
									+ evt.getId_enseignant()
									+ ", "
									+ evt.getId_matiere()
									+ ", "
									+ evt.getId_groupe()
									+ ", "
									+ "\""
									+ Util.getTimeStampByJavaDate(evt
											.getStart())
									+ "\", "
									+ "\""
									+ Util.getTimeStampByJavaDate(evt.getEnd())
									+ "\", "
									+ "\""
									+ evt.getCommentaire()
									+ "\", "
									+ evt.getId_evenement_recurrence()
									+ ")");
		}
	}

	public boolean delete(int idEvt) {
		boolean deleted = true;

		try {

			connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY).executeUpdate(
					"DELETE FROM EVENEMENT WHERE ID_EVENEMENT = "
							+ String.valueOf(idEvt));
		} catch (SQLException e) {
			e.printStackTrace();
			deleted = false;
		}

		return deleted;
	}

	public List<Evenement> findAll() throws SQLException {
		ResultSet result = connect
				.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY)
				.executeQuery(
						"SELECT ID_EVENEMENT, ID_SALLE, ID_TYPE, ID_ENSEIGNANT, ID_MATIERE, ID_GROUPE, DATE_DEBUT, DATE_FIN, COMMENTAIRE, ID_EVENEMENT_RECURRENCE FROM EVENEMENT");

		Evenement evt;
		List<Evenement> all = new ArrayList<Evenement>();
		while (result.next()) {

			evt = new Evenement(result.getInt("ID_EVENEMENT"), result
					.getInt("ID_SALLE"), result.getInt("ID_TYPE"), result
					.getInt("ID_ENSEIGNANT"), result.getInt("ID_MATIERE"),
					result.getInt("ID_GROUPE"),
					result.getString("COMMENTAIRE"), Util.getDateBySQL(result
							.getTimestamp("DATE_DEBUT")), Util
							.getDateBySQL(result.getTimestamp("DATE_FIN")),
					result.getInt("ID_EVENEMENT_RECURRENCE"));
			evt.configureAppointment(DAOFactory.getEnseignantDao().findById(
					result.getInt("ID_ENSEIGNANT")).getNom(), DAOFactory
					.getMatiereDao().findById(result.getInt("ID_MATIERE"))
					.getIntitule(), DAOFactory.getGroupeDao().findById(
					result.getInt("ID_GROUPE")).getIntitule(), DAOFactory
					.getSalleDao().findById(result.getInt("ID_SALLE"))
					.getIntitule(), DAOFactory.getGroupeDao().findById(
					result.getInt("ID_GROUPE")).getColor());
			all.add(evt);
		}

		result.close();

		return all;
	}

	public Evenement findById(int id) throws SQLException {
		ResultSet result = connect.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
				.executeQuery(
						"SELECT * FROM EVENEMENT WHERE ID_EVENEMENT = " + id);

		Evenement evt;

		if (result.next()) {
			int idEvenementRecurrence = result
					.getInt("ID_EVENEMENT_RECURRENCE");

			evt = new Evenement(result.getInt("ID_EVENEMENT"), result
					.getInt("ID_SALLE"), result.getInt("ID_TYPE"), result
					.getInt("ID_ENSEIGNANT"), result.getInt("ID_MATIERE"),
					result.getInt("ID_GROUPE"),
					result.getString("COMMENTAIRE"), Util.getDateBySQL(result
							.getTimestamp("DATE_DEBUT")), Util
							.getDateBySQL(result.getTimestamp("DATE_FIN")),
					idEvenementRecurrence);

			evt.configureAppointment(DAOFactory.getEnseignantDao().findById(
					result.getInt("ID_ENSEIGNANT")).getNom(), DAOFactory
					.getMatiereDao().findById(result.getInt("ID_MATIERE"))
					.getIntitule(), DAOFactory.getGroupeDao().findById(
					result.getInt("ID_GROUPE")).getIntitule(), DAOFactory
					.getSalleDao().findById(result.getInt("ID_SALLE"))
					.getIntitule(), DAOFactory.getGroupeDao().findById(
					result.getInt("ID_GROUPE")).getColor());

			result.close();

			if (evt.getId_evenement_recurrence() != 0) {
				// début et fin de récurrence
				result = connect
						.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
								ResultSet.CONCUR_READ_ONLY)
						.executeQuery(
								"SELECT MIN(DATE_DEBUT) AS DEBUT, "
										+ "MAX(DATE_FIN) AS FIN "
										+ "FROM EVENEMENT WHERE ID_EVENEMENT_RECURRENCE = "
										+ idEvenementRecurrence);

				if (result.next()) {
					evt.setRecurrence_debut(Util.getDateBySQL(result
							.getTimestamp("DEBUT")));
					evt.setRecurrence_fin(Util.getDateBySQL(result
							.getTimestamp("FIN")));

					result.close();
				}

				// Liste des jours cochés
				result = connect.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY).executeQuery(
						"SELECT DISTINCT (DAYOFWEEK(DATE_DEBUT) -1)"
								+ " AS JOUR FROM EVENEMENT "
								+ "WHERE ID_EVENEMENT_RECURRENCE = "
								+ idEvenementRecurrence);

				ArrayList<Integer> joursCoches = new ArrayList<Integer>();
				while (result.next()) {
					joursCoches.add(result.getInt("JOUR"));
				}
				result.close();

				evt.setRecurrence_jours(joursCoches);
			}
		} else {
			evt = new Evenement();
		}

		return evt;
	}

	/**
	 * 
	 * @return : dates by pair (end of the first event, start of the second)
	 */
	public ArrayList<Date> findCreneauxLibres(ArrayList<Integer> groupes,
			Date dateDebut, int heureDebut, int minDebut, int heureFin,
			int minFin) {
		/**
		 * Restrict the sql order to the checked groupes
		 */
		String sqlGroupes = " AND (";
		for (int i = 0; i < groupes.size(); ++i) {
			sqlGroupes += " ID_GROUPE = " + groupes.get(i);

			if (i < groupes.size() - 1) {
				sqlGroupes += " OR ";
			}
		}
		sqlGroupes += ")";

		/**
		 * Select all the events - from dateDebut until the week after - that
		 * start after the given time
		 */
		String sql = "SELECT * FROM EVENEMENT "
				+ "WHERE (EXTRACT(HOUR FROM DATE_DEBUT) * 60 + EXTRACT(MINUTE FROM DATE_DEBUT)) BETWEEN "
				+ "("
				+ heureDebut
				+ " * 60 + "
				+ minDebut
				+ ") AND ("
				+ heureFin
				+ " * 60 + "
				+ minFin
				+ ") "
				+ " AND DATE_DEBUT BETWEEN \""
				+ Util.getTimeStampByJavaDate(dateDebut)
				+ "\" AND \""
				+ Util.getTimeStampByJavaDate(new Date(dateDebut.getTime()
						+ 1000 * 60 * 60 * 24 * 7)) + "\"" + sqlGroupes
				+ " ORDER BY DATE_DEBUT ASC";

		System.out.println(sql);

		ArrayList<Date> creneaux = new ArrayList<Date>();

		try {
			ResultSet result = connect.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY).executeQuery(sql);

			result.last();
			int rowCount = result.getRow();

			if (rowCount == 0) {
				creneaux.add(dateDebut);
				Date dateFin = (Date) (dateDebut.clone());
				dateFin.setHours(heureFin);
				dateFin.setMinutes(minFin);
				creneaux.add(dateFin);

			} else if (rowCount == 1) {
				result.next();
				creneaux
						.add(Util.getDateBySQL(result.getTimestamp("DATE_FIN")));
				Date dateFin = (Date) (dateDebut.clone());
				dateFin.setHours(heureFin);
				dateFin.setMinutes(minFin);
				creneaux.add(dateFin);
				result.close();

			} else {

				/**
				 * Store the first event, will then calculate the free time
				 * between two until we find one with enough time
				 * 
				 */
				result.first();
				Evenement evt1 = new Evenement(result.getInt("ID_EVENEMENT"),
						result.getInt("ID_SALLE"), result.getInt("ID_TYPE"),
						result.getInt("ID_ENSEIGNANT"), result
								.getInt("ID_MATIERE"), result
								.getInt("ID_GROUPE"), result
								.getString("COMMENTAIRE"),
						Util.getDateBySQL(result.getTimestamp("DATE_DEBUT")),
						Util.getDateBySQL(result.getTimestamp("DATE_FIN")),
						result.getInt("ID_EVENEMENT_RECURRENCE"));

				dateDebut.setHours(heureDebut);
				dateDebut.setMinutes(minDebut);

				if (evt1.getStart().after(dateDebut)) {
					creneaux.add(dateDebut);
					creneaux.add(evt1.getStart());
				} else {

					Date dateFin;

					Evenement evt2;

					while (result.next()) {
						evt2 = new Evenement(result.getInt("ID_EVENEMENT"),
								result.getInt("ID_SALLE"), result
										.getInt("ID_TYPE"), result
										.getInt("ID_ENSEIGNANT"), result
										.getInt("ID_MATIERE"), result
										.getInt("ID_GROUPE"), result
										.getString("COMMENTAIRE"), Util
										.getDateBySQL(result
												.getTimestamp("DATE_DEBUT")),
								Util.getDateBySQL(result
										.getTimestamp("DATE_FIN")), result
										.getInt("ID_EVENEMENT_RECURRENCE"));

						if (evt1.getEnd().before(evt2.getStart())
								&& (evt1.getEnd().equals(evt2.getStart()) == false)) {

							System.out.println(evt1.getEnd() + " "
									+ evt2.getStart());

							// the rest of the day is free, we add 2 events
							if (evt2.getStart().getDate() > evt1.getEnd()
									.getDate()) {

								creneaux.add(evt1.getEnd());

								dateFin = (Date) (evt1.getEnd().clone());

								dateFin.setHours(heureFin);
								dateFin.setMinutes(minFin);
								creneaux.add(dateFin);

								dateDebut = (Date) (evt2.getStart().clone());

								dateDebut.setMinutes(minDebut);
								dateDebut.setHours(heureDebut);

								if (dateDebut.before(evt2.getStart())) {
									creneaux.add(dateDebut);
									creneaux.add(evt2.getStart());
								}

							} else if (evt2.getStart().after(evt1.getEnd())) {

								creneaux.add(evt1.getEnd());
								creneaux.add(evt2.getStart());
							}
						}

						evt1.setStart(evt2.getStart());
						evt1.setEnd(evt2.getEnd());
					}
					result.close();
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return creneaux;
	}

	public List<Evenement> findEventBetween2DateAndWithCriteria(int idGroupe,
			int idEnseignant, int idSalle, int idMatiere,
			Date dateDebutSemaine, Date dateFinSemaine) throws SQLException {

		boolean moreThanOneField = false;

		String query = "SELECT * FROM EVENEMENT WHERE ";

		if (idGroupe > 0) {
			query = query + " ID_GROUPE = " + idGroupe + " ";
			moreThanOneField = true;
		}

		if (idEnseignant > 0) {
			if (moreThanOneField) {
				query = query + " AND ";
			}
			moreThanOneField = true;
			query = query + "	ID_ENSEIGNANT=" + idEnseignant + " ";
		}

		if (idSalle > 0) {
			if (moreThanOneField) {
				query = query + " AND ";
			}
			moreThanOneField = true;
			query = query + " ID_SALLE=" + idSalle + " ";
		}

		if (idMatiere > 0) {
			if (moreThanOneField) {
				query = query + " AND ";
			}
			moreThanOneField = true;
			query = query + " ID_MATIERE=" + idMatiere + " ";
		}

		if (moreThanOneField) {
			query = query + " AND ";
		}

		query = query + " DATE_DEBUT BETWEEN \""
				+ Util.getTimeStampByJavaDate(dateDebutSemaine) + "\" AND \""
				+ Util.getTimeStampByJavaDate(dateFinSemaine) + "\"";

		System.out.println(query);
		List<Evenement> events = new ArrayList<Evenement>();
		ResultSet result = connect.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
				.executeQuery(query);

		Evenement evt;
		while (result.next()) {
			evt = new Evenement(result.getInt("ID_EVENEMENT"), result
					.getInt("ID_SALLE"), result.getInt("ID_TYPE"), result
					.getInt("ID_ENSEIGNANT"), result.getInt("ID_MATIERE"),
					result.getInt("ID_GROUPE"),
					result.getString("COMMENTAIRE"), Util.getDateBySQL(result
							.getTimestamp("DATE_DEBUT")), Util
							.getDateBySQL(result.getTimestamp("DATE_FIN")),
					result.getInt("ID_EVENEMENT_RECURRENCE"));
			events.add(evt);

		}
		result.close();
		return events;

	}

	public int findRecurrenceId() throws SQLException {
		ResultSet result = connect
				.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY)
				.executeQuery(
						"SELECT MAX(ID_EVENEMENT_RECURRENCE) as  ID_EVENEMENT_RECURRENCE FROM EVENEMENT");

		int id_evenement_recurrence_next = 0;
		while (result.next()) {
			id_evenement_recurrence_next = result
					.getInt("ID_EVENEMENT_RECURRENCE") + 1;
		}
		result.close();
		return id_evenement_recurrence_next;
	}

	public void update(Evenement evt) throws SQLException {
		if ((evt instanceof Evenement) && !evt.equals(null)) {
			System.out.println("UPDATE EVENEMENT SET ID_SALLE="
					+ evt.getId_salle() + ", ID_TYPE=" + evt.getId_type()
					+ ",ID_ENSEIGNANT=" + evt.getId_enseignant()
					+ ", ID_MATIERE=" + evt.getId_matiere() + ", ID_GROUPE="
					+ evt.getId_groupe() + "" + ", DATE_DEBUT=" + "\""
					+ Util.getTimeStampByJavaDate(evt.getStart()) + "\" "
					+ ",DATE_FIN=" + "\""
					+ Util.getTimeStampByJavaDate(evt.getEnd()) + "\" "
					+ ",COMMENTAIRE=" + "\"" + evt.getCommentaire() + "\" "
					+ ",ID_EVENEMENT_RECURRENCE="
					+ evt.getId_evenement_recurrence()
					+ " WHERE ID_EVENEMENT = " + evt.getId());
			connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeUpdate(
					"UPDATE EVENEMENT SET ID_SALLE=" + evt.getId_salle()
							+ ", ID_TYPE=" + evt.getId_type()
							+ ",ID_ENSEIGNANT=" + evt.getId_enseignant()
							+ ", ID_MATIERE=" + evt.getId_matiere()
							+ ", ID_GROUPE=" + evt.getId_groupe() + ""
							+ ", DATE_DEBUT=" + "\""
							+ Util.getTimeStampByJavaDate(evt.getStart())
							+ "\" " + ", DATE_FIN=" + "\""
							+ Util.getTimeStampByJavaDate(evt.getEnd()) + "\" "
							+ ", COMMENTAIRE=" + "\" " + evt.getCommentaire()
							+ "\" " + " WHERE ID_EVENEMENT= " + evt.getId()

			);
		}
	}

	public boolean updateEventDate(int id_event, Date debut, Date fin) {

		try {
			connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeUpdate(
					"UPDATE EVENEMENT SET DATE_DEBUT='"
							+ Util.getTimeStampByJavaDate(debut)
							+ "' , DATE_FIN='"
							+ Util.getTimeStampByJavaDate(fin)
							+ "' WHERE ID_EVENEMENT =" + id_event + ";");

			return true;
		} catch (SQLException e) {
			return false;
		}

	}

}
